package helpers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SqlHelperImpl implements SqlHelper {

    public void removePayrollDuplicates() throws Exception {
        /*
         * Connect to database.
         */
        Class.forName("com.mysql.jdbc.Driver");
        Connection connect = DriverManager
                .getConnection("jdbc:mysql://localhost/mp_register?"
                        + "user=root&password=root");

        /*
         * Select all employee records with double payroll within 1 payroll
         * period.
         */
        Statement statement = connect.createStatement();
        ResultSet rs = statement.executeQuery("select * from payroll");

        List<PayrollSql> payrolls = new ArrayList<PayrollSql>();
        List<PayrollSql> duplicates = new ArrayList<PayrollSql>();
        while (rs.next()) {
            PayrollSql payrollSql = new PayrollSql();
            payrollSql.id = rs.getLong("id");
            payrollSql.employeeId = rs.getLong("employee_id");
            payrollSql.payrollPeriodId = rs.getLong("payrollPeriod_id");

            if (payrolls.contains(payrollSql)) {
                duplicates.add(payrollSql);
            } else {
                payrolls.add(payrollSql);
            }
        }

        System.out.println("PAYROLLS");
        for (PayrollSql p : payrolls) {
            System.out.println("ID: " + p.id + ", EMP_ID: " + p.employeeId
                    + ", PERIOD_ID: " + p.payrollPeriodId);
        }

        System.out.println("DUPLICATES");
        for (PayrollSql dup : duplicates) {
            System.out.println("ID: " + dup.id + ", EMP_ID: " + dup.employeeId
                    + ", PERIOD_ID: " + dup.payrollPeriodId);
        }

        /*
         * Delete the first entry that has been duplicated.
         */
        for (PayrollSql p : payrolls) {
            if (duplicates.contains(p)) {
                PreparedStatement stmt = connect
                        .prepareStatement("delete from payment where payroll_id=?");
                stmt.setLong(1, p.id);
                stmt.executeUpdate();

                stmt = connect
                        .prepareStatement("delete from deduction where payroll_id=?");
                stmt.setLong(1, p.id);
                stmt.executeUpdate();

                stmt = connect
                        .prepareStatement("delete from payroll where id=?");
                stmt.setLong(1, p.id);
                stmt.executeUpdate();
            }
        }

        connect.close();
    }

    public static void main(String[] args) throws Exception {
    	SqlHelper sqlHelper = new SqlHelperImpl();
    	sqlHelper.removePayrollDuplicates();
    }
    
}
